package sk.velkavrana.app.util;

import java.io.Reader;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

/**
 * User: mtymes
 * Date: Feb 18, 2009
 * Time: 2:26:43 PM
 */
public class DBUtilV2 {

    /**
     * will create statement
     *
     * @param conn connection to database
     * @return statement or null if the process will fail
     */
    public static Statement createStatement(Connection conn) {
        Statement stmt = null;

        try {
            stmt = conn.createStatement();
        }
        catch (SQLException e) {
            e.printStackTrace();
        }

        return stmt;
    }


    /**
     * will create prepared statement
     *
     * @param conn        connection to database
     * @param queryString query string which will be used for creating of prepared statement
     * @return prepared statement or null if the process will fail
     */
    public static PreparedStatement createPreparedStatement(Connection conn, String queryString) {
        PreparedStatement pstmt = null;

        try {
            pstmt = conn.prepareStatement(queryString);
        }
        catch (SQLException e) {
            e.printStackTrace();
        }

        return pstmt;
    }


    /**
     * will fill the prepared statement with list of values
     *
     * @param pstmt  prepared statement that needs to be filled
     * @param values list of value that will used for filling of prepared statement
     * @return boolean identifier if the filling process was sucessfull
     */
    public static boolean fillInPreparedStatement(PreparedStatement pstmt, List values, List<Integer> types) {
        try {
            for (int i = 0; i < values.size(); i++) {
                Object value = values.get(i);

/*
                if ( value instanceof String ) {
                    pstmt.setString(i + 1, (String) value);
                }
                else if ( value instanceof Integer ) {
                    pstmt.setInt(i + 1, ((Integer) value).intValue());
                }
                else if ( value instanceof Boolean ) {
                    pstmt.setBoolean(i + 1, ((Boolean) value).booleanValue());
                }
                else if ( value instanceof java.util.Date ) {
                    pstmt.setTimestamp(i + 1, new Timestamp(
                            ((java.util.Date) value).getTime()));
                }
                else if ( value instanceof BigDecimal ) {
                    pstmt.setBigDecimal(i + 1, (BigDecimal) value);
                }
                else if ( value instanceof Double ) {
                    pstmt.setDouble(i + 1, ((Double) value).doubleValue());
                }
                else if ( value == null ) {
                    // todo: implement types storage
                    int sqlType = Types.VARCHAR;
                    pstmt.setNull(i + 1, sqlType);
                }
                else {
                    pstmt.setObject(i + 1, value);
                }
*/
                if (value == null) {
                    pstmt.setNull(i + 1, types.get(i));
                } else if (value instanceof Reader) {
                    pstmt.setCharacterStream(i + 1, (Reader) value);
                } else {
                    pstmt.setObject(i + 1, value);
                }
            }
        }
        catch (SQLException e) {
            e.printStackTrace();

            return false;
        }

        return true;
    }


    /**
     * will execute the prepared statement as an update
     *
     * @param pstmt prepared statement that will be executed
     * @return boolean identifier if the update was succesfull
     */
    public static boolean executeUpdate(PreparedStatement pstmt) {
        try {
            pstmt.executeUpdate();
        }
        catch (SQLException e) {
            e.printStackTrace();

            return false;
        }

        return true;
    }


    // todo: add commentary
    public static boolean executeUpdate(Statement stmt, String query) {
        try {
            stmt.executeUpdate(query);
        }
        catch (SQLException e) {
            e.printStackTrace();

            return false;
        }

        return true;
    }


    // todo: add commentary
    public static boolean executeUpdate(Connection conn, String query) {
        boolean returnValue = false;

        Statement stmt = createStatement(conn);

        if (stmt != null) {
            returnValue = executeUpdate(stmt, query);

            close(stmt);
        }

        return returnValue;
    }


    /**
     * will insert new record into the database
     *
     * @param conn      connection to database
     * @param tableName table name
     * @param fieldMap  map that holds fields and its values that should be stored into the database
     */
    public static void insertRecord(Connection conn, String tableName, Map<String, Object> fieldMap, Map<String, Integer> colTypes) {
        Object[] fields = fieldMap.keySet().toArray();
        if (fields != null && fields.length > 0) {

            // create insert
            StringBuffer queryString = new StringBuffer("INSERT INTO " + tableName + " (");
            StringBuffer valuePart = new StringBuffer(" VALUES (");
            List<Object> values = new ArrayList<Object>();
            List<Integer> types = new ArrayList<Integer>();

            for (int i = 0; i < fields.length; i++) {
                if (i > 0) {
                    queryString.append(", ");
                    valuePart.append(", ");
                }
//                queryString.append("\"" + fields[i] + "\"");
                queryString.append(fields[i]);
                valuePart.append("?");
                values.add(fieldMap.get(fields[i]));
                types.add(colTypes.get(fields[i]));
            }
            valuePart.append(")");
            queryString.append(") ").append(valuePart);

            // execute insert
            PreparedStatement pstmt = createPreparedStatement(conn,
                    queryString.toString());
            if (pstmt != null) {
                if (fillInPreparedStatement(pstmt, values, types)) {

                    executeUpdate(pstmt);

                }
                close(pstmt);
            }
        }
    }


    /**
     * will close statement
     *
     * @param stmt statement
     * @return boolean identifier if the closing process was sucessfull
     */
    public static boolean close(Statement stmt) {
        try {
            stmt.close();
        }
        catch (SQLException e) {
            e.printStackTrace();

            return false;
        }

        return true;
    }
}
